package gdufs.stat.service;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class FwtjQuery {
	Log logger = LogFactory.getLog(FwtjQuery.class);

	private String[] weekDescArr = new String[] { "星期一", "星期二", "星期三", "星期四",
			"星期五", "星期六", "星期天" };

	private String[] monthDescArr = new String[] { "一月", "二月", "三月", "四月",
			"五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月" };

	private String[] periodDescArr = new String[] { "上旬", "中旬", "下旬" };

	private String[] typeDescArr = new String[]{"教师","研究生","本科生"};
	
	private SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyy-MM-dd");

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public String getWeekStatics(String deptCode) {
		String weekStatics = "<chart caption='综合访问统计' xAxisName='时间' yAxisName='次数'>";

		GregorianCalendar calendar = new GregorianCalendar();
		int minus = calendar.get(GregorianCalendar.DAY_OF_WEEK) + 1;
		calendar.add(GregorianCalendar.DATE, -minus);
		calendar.add(GregorianCalendar.DATE, -4);

		for (int i = 0; i < 7; i++) {

			int count = getWeekStaticsAccount(deptCode, calendar.getTime());
			weekStatics += "<set label='" + weekDescArr[i] + "' value='"
					+ count + "' />";

			calendar.add(GregorianCalendar.DATE, 1);
			new java.sql.Date(calendar.getTime().getTime());
		}
		weekStatics += "</chart>";
		return weekStatics;
	}

	private int getWeekStaticsAccount(String deptCode,
			java.util.Date staticsDate) {
		String sql = "";
		Object[] paramObj = null;
		Date stime = null;
		Date etime = null;

		GregorianCalendar calendar = new GregorianCalendar();
		calendar.setTime(staticsDate);
		stime = new Date(DateUtils.truncate(staticsDate, Calendar.DATE)
				.getTime());
		calendar.add(GregorianCalendar.DAY_OF_MONTH, 1);
		etime = new Date(DateUtils.truncate(calendar.getTime(), Calendar.DATE)
				.getTime());

		if (deptCode == null && "".equals(deptCode)) {
			sql = "select count(*) as amount from t_portal_session t where t.logintime >= ? and t.logintime < ?";
			paramObj = new Object[] { stime, etime };
		} else {
			sql = "select count(*) as amount from t_portal_session t where t.deptcode = ? and t.logintime >= ? and t.logintime < ?";
			paramObj = new Object[] { deptCode, stime, etime };
		}
		return (Integer) jdbcTemplate.queryForObject(sql, paramObj,
				new RowMapper() {
					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						return rs.getInt("amount");
					}
				});
	}

	public String getMonthStatics(String deptCode) {
		String monthStatics = "<chart caption='综合访问统计' xAxisName='时间' yAxisName='次数'>";

		int periodStaticsAccount = 0;
		for (int i = 1; i < 4; i++) {
			periodStaticsAccount = getMonthStaticsAccount(deptCode, i);
			monthStatics += "<set label='" + periodDescArr[i - 1] + "' value='"
					+ periodStaticsAccount + "' />";
		}

		monthStatics += "</chart>";
		return monthStatics;
	}

	private int getMonthStaticsAccount(String deptCode, int period) {
		String sql = "";
		Object[] paramObj = null;
		Date stime = null;
		Date etime = null;

		Calendar calendar = Calendar.getInstance();
		int month = calendar.get(Calendar.MONTH);
		if (1 == period) {
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			DateUtils.truncate(calendar, Calendar.DATE);
			stime = new Date(calendar.getTime().getTime());

			calendar.set(Calendar.DAY_OF_MONTH, 10);
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			etime = new Date(DateUtils.truncate(calendar,Calendar.DATE).getTime().getTime());
		} else if (2 == period) {
			calendar.set(Calendar.DAY_OF_MONTH, 11);
			DateUtils.truncate(calendar, Calendar.DATE);
			stime = new Date(calendar.getTime().getTime());

			calendar.set(Calendar.DAY_OF_MONTH, 20);
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			etime = new Date(DateUtils.truncate(calendar,Calendar.DATE).getTime().getTime());
		} else {
			calendar.set(Calendar.DAY_OF_MONTH, 21);
			DateUtils.truncate(calendar, Calendar.DATE);
			stime = new Date(calendar.getTime().getTime());

			calendar.add(Calendar.MONTH, 1);
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			etime = new Date(calendar.getTime().getTime());
		}

		if (deptCode == null || deptCode.trim().length() <= 0) {
			sql = "select count(*) as amount from t_portal_session t where t.logintime >= ? and t.logintime < ?";
			paramObj = new Object[] { stime, etime };
		} else {
			sql = "select count(*) as amount from t_portal_session t where t.deptcode = ? and t.logintime >= ? and t.logintime < ?";
			paramObj = new Object[] { deptCode, stime, etime };
		}
		return (Integer) jdbcTemplate.queryForObject(sql, paramObj,
				new RowMapper() {
					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						return rs.getInt("amount");
					}
				});
	}

	public String getYearStatics(String deptCode) {
		String yearStatics = "<chart caption='综合访问统计' xAxisName='时间' yAxisName='次数'>";
		int monthStaticsAccount = 0;
		for (int i = 0; i < 12; i++) {

			monthStaticsAccount = getYearStaticsAccount(deptCode, i);
			yearStatics += "<set label='" + monthDescArr[i] + "' value='"
					+ monthStaticsAccount + "' />";
		}
		yearStatics += "</chart>";
		return yearStatics;
	}

	private int getYearStaticsAccount(String deptCode, int staticsMonth) {
		String sql = "";
		Object[] paramObj = null;
		Date[] date = getFirstAndLastDate(staticsMonth);
		if (deptCode == null || deptCode.trim().length() <= 0) {
			sql = "select count(*) as amount from t_portal_session t where t.logintime >= ? and t.logintime < ? ";
			paramObj = new Object[] { date[0],date[1] };
		} else {
			sql = "select count(*) as amount from t_portal_session t where t.deptcode = ? and t.logintime >= ? and t.logintime < ?";
			paramObj = new Object[] { deptCode, date[0],date[1] };
		}
		return (Integer) jdbcTemplate.queryForObject(sql, paramObj,
				new RowMapper() {
					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						return rs.getInt("amount");
					}
				});
	}

	public List getDeptVisitStatics(String metier, String startTime,
			String endTime) {
		final List<Map> deptVisitStatics = new ArrayList<Map>();
		Object[] paramsObj = null;
		String sql = "";
		String allAccountSql = "";
		Date[] date = translate(startTime, endTime);

		if (metier != null && !"0".equals(metier)) {
			sql = "select v.deptcode deptcode,v.deptname deptname,count(distinct v.userid) userAccount,count(*) visitAccount from t_portal_session v where v.metier = ? and v.logintime between ? and ? group by v.deptcode,v.deptname";
			allAccountSql = "select count(*) allAccount from t_portal_session v where v.metier = ? and v.logintime between ? and ? ";
			paramsObj = new Object[] { metier, date[0], date[1] };
		} else {
			sql = "select v.deptcode deptcode,v.deptname deptname,count(distinct v.userid) userAccount,count(*) visitAccount from t_portal_session v where v.logintime between ? and ? group by v.deptcode,v.deptname";
			allAccountSql = "select count(*) allAccount from t_portal_session v where v.logintime between ? and ? ";
			paramsObj = new Object[] { date[0],date[1] };
		}

		final Integer allAccount = (Integer) jdbcTemplate.queryForObject(
				allAccountSql, paramsObj, new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						return rs.getInt("allAccount");
					}
				});

		jdbcTemplate.query(sql, paramsObj, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Map deptVisitStaticsItem = new HashMap();
				String deptcode = rs.getString("deptcode");
				String deptname = rs.getString("deptname");
				String userAccount = rs.getString("userAccount");
				int visitAccount = rs.getInt("visitAccount");

				deptVisitStaticsItem.put("deptcode", deptcode);
				deptVisitStaticsItem.put("deptname", deptname);
				deptVisitStaticsItem.put("userAccount", userAccount);
				deptVisitStaticsItem.put("visitAccount", visitAccount);
				deptVisitStaticsItem.put("percentage", visitAccount
						/ (double) allAccount);
				deptVisitStatics.add(deptVisitStaticsItem);
				return null;
			}

		});

		return deptVisitStatics;

	}

	public List getDetailByDeptCode(String deptcode, String metier,
			String starttime, String endtime) {
		final List userStatics = new ArrayList();
		if (deptcode == null || "".equals(deptcode)) {
			return userStatics;
		}

		String userStaticsSql = "";
		String deptAccountStaticsSql = "";
		Object[] paramsObj = null;
		Date stime = null;
		Date etime = null;
		final Integer deptAccount;
		try {
			stime = new Date(sDateFormat.parse(starttime).getTime());
			etime = new Date(sDateFormat.parse(endtime).getTime());
		} catch (ParseException e) {
			e.printStackTrace();
			try {
				stime = new Date(sDateFormat.parse("2010-1-1").getTime());
			} catch (ParseException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			etime = new Date(new java.util.Date().getTime());
		}

		if (metier == null && !"0".equals(metier)) {
			userStaticsSql = "select v.userid userid,v.username username,count(*) visitAccount from t_portal_session v where v.deptcode = ? and v.metier = ? and v.logintime between ? and ? group by v.userid,v.username";
			deptAccountStaticsSql = "select count(*) deptAccount from  t_portal_session v where v.deptcode = ? and v.metier = ? and v.logintime between ? and ?";
			paramsObj = new Object[] { deptcode, metier, stime, etime };
		} else {
			userStaticsSql = "select v.userid userid,v.username username,count(*) visitAccount from t_portal_session v where v.deptcode = ? and v.logintime between ? and ? group by v.userid,v.username";
			deptAccountStaticsSql = "select count(*) deptAccount from  t_portal_session v where v.deptcode = ? and v.logintime between ? and ?";
			paramsObj = new Object[] { deptcode, stime, etime };
		}

		deptAccount = (Integer) jdbcTemplate.queryForObject(
				deptAccountStaticsSql, paramsObj, new RowMapper() {

					public Object mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						return rs.getInt("deptAccount");
					}
				});

		jdbcTemplate.query(userStaticsSql, paramsObj, new RowMapper() {

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				Map userStaticsItem = new HashMap();
				String userid = rs.getString("userid");
				String username = rs.getString("username");
				int visitAccount = rs.getInt("visitAccount");

				userStaticsItem.put("userid", userid);
				userStaticsItem.put("username", username);
				userStaticsItem.put("visitAccount", visitAccount);
				userStaticsItem.put("percentage", visitAccount / deptAccount);
				userStatics.add(userStaticsItem);
				return null;
			}
		});

		return userStatics;
	}

	// 按用户类别统计
	public String getTypeStatics(String startTime, String endTime) {
		String typeStatics = "<chart caption='按身份类别综合访问统计' xAxisName='类别' yAxisName='次数'>";
		int typeStaticsAccount = 0;
		Date[] date = translate(startTime, endTime);
		
		for (int i = 1; i < 4; i++) {
			typeStaticsAccount = getTypeStaticsAccount(i,date[0],date[1]);
			typeStatics += "<set label='" + typeDescArr[i - 1] + "' value='"
			+ typeStaticsAccount + "' />";
		}
		typeStatics += "</chart>";
		return typeStatics;
	}

	private int getTypeStaticsAccount(int metier, Date stime, Date etime) {
		return (Integer) jdbcTemplate
				.queryForObject(
						"select count(*) as typeAccount from t_portal_session t where t.metier = ? and t.logintime >= ? and t.logintime <= ?",
						new Object[] { metier, stime, etime }, new RowMapper() {

							public Object mapRow(ResultSet as, int arg1)
									throws SQLException {
								// TODO Auto-generated method stub
								return as.getInt("typeAccount");
							}
						});
	}

	private Date[] translate(String startTime, String endTime){
		Date[] date = new Date[2];
		Date stime = null;
		Date etime = null;
		
		
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.WEEK_OF_MONTH, -1);
		calendar.add(Calendar.DAY_OF_WEEK, 1);
		
		if (startTime == null || startTime.trim().length() <= 0) {
			stime = new Date(DateUtils.truncate(calendar, Calendar.DATE).getTime().getTime());
		} else {
			try {
				stime = new Date(sDateFormat.parse(startTime).getTime());
			} catch (ParseException e) {
				e.printStackTrace();
				stime = new Date(DateUtils.truncate(calendar, Calendar.DATE).getTime().getTime());
			}
		}
		if (endTime == null || endTime.trim().length() <= 0) {
			etime = new Date(new java.util.Date().getTime());
		} else {
			try {
				etime = new Date(sDateFormat.parse(endTime).getTime());
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				etime = new Date(new java.util.Date().getTime());
			}
		}
		date[0] = stime;
		date[1] = etime;
		return date;
	}
	
	private Date[] getFirstAndLastDate(int month){
		Date[] date = new Date[2];
		Date stime = null;
		Date etime = null;
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.MONTH, month);
		calendar.set(Calendar.DAY_OF_MONTH, 1);
		
		date[0] = new Date(DateUtils.truncate(calendar, Calendar.DATE).getTime().getTime());
		calendar.add(Calendar.MONTH, 1);
		//calendar.set(Calendar.DAY_OF_MONTH, -1);
		date[1] = new Date(DateUtils.truncate(calendar, Calendar.DATE).getTime().getTime());
		return date;
		
	}
	
	public List<Map> getTypeStaticsDetail(String startTime, String endTime) {
		final List<Map> typeStaticsList = new ArrayList<Map>();
		Object[] paramsObj = null;
		Date[] date = translate(startTime,endTime);
		paramsObj = new Object[]{date[0],date[1]};
		final int allAccount = (Integer)jdbcTemplate.queryForObject("select count(t.sessionid) allAccount from t_portal_session t where t.logintime >= ? and t.logintime < ?",paramsObj,new RowMapper() {
			
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getInt("allAccount");
			}
		});
		
		jdbcTemplate.query("select t.metier metier,count(distinct userid) userAcount,count(t.sessionid) visitAccount from t_portal_session t where t.logintime >= ? and t.logintime < ? group by t.metier", paramsObj,new RowMapper() {
			
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				Map typeStaticsItem = new HashMap();
				int metier = rs.getInt("metier");
				int visitAccount = rs.getInt("visitAccount");
				int userAccount = rs.getInt("userAcount");
				typeStaticsItem.put("metier", metier);
				typeStaticsItem.put("visitAccount",visitAccount);
				typeStaticsItem.put("userAccount", userAccount);
				typeStaticsItem.put("percentage",visitAccount/(double)allAccount);
				
				typeStaticsList.add(typeStaticsItem);
				return null;
			}
		});
		return typeStaticsList;
	}

	public List<Map> queryVisitList(String metier,String startTime,String endTime,String userid,String username,String deptName){
		final List<Map> queryList = new ArrayList<Map>();
		String sql = "select tps.deptcode,tps.deptname,tps.userid,tps.username,tps.metier,tps.logintime,tps.logouttime,tps.host,tps.loginip from t_portal_session tps where 1 = 1 ";

		if(!StringUtils.isEmpty(metier) && !"0".equals(metier)){
			sql += " and tps.metier = "+metier+" ";
		}
		if(!StringUtils.isEmpty(startTime)){
			sql += " and tps.logintime >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(!StringUtils.isEmpty(endTime)){
			sql += " and tps.logintime <= to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(!StringUtils.isEmpty(userid)){
			sql += " and tps.userid = '"+userid+"' ";
		}
		if(!StringUtils.isEmpty(username)){
			sql += " and tps.username = '"+username+"' ";
		}
		if(!StringUtils.isEmpty(deptName)){
			sql += " and tps.deptname = '"+deptName+"' ";
		}
		
	//	logger.info(sql);
		
		jdbcTemplate.query(sql,new RowMapper() {
			
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Map item = new HashMap();
				item.put("deptcode",rs.getString("deptcode") );
				item.put("deptname",rs.getString("deptname") );
				item.put("userid",rs.getString("userid") );
				item.put("username",rs.getString("username") );
				item.put("metier",rs.getString("metier") );
				item.put("logintime",rs.getString("logintime") );
				item.put("logouttime",rs.getString("logouttime") );
				item.put("host",rs.getString("host") );
				item.put("loginip",rs.getString("loginip") );
				queryList.add(item);
				return null;
			}
		});
		return queryList;
	} 
	
	
}
